package com.shycloud.mido.organ.test;

import com.alibaba.fastjson.JSONObject;
import com.shycloud.mido.organ.test.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Xiaoxing Yu
 * @date 2020/6/23
 **/
public class FixMusicUnableToLoadAfterScanCode {

	public static void main(String[] args) {
		String phone = "13607173210";
		String phone2 = "18917593473";
//		dealWithCollectionName("巴斯蒂安钢琴教程基础2");
//		dealWithCollectionName("巴斯蒂安钢琴教程基础3");
//		dealWithCollectionName("巴斯蒂安钢琴教程技巧1");
//		dealWithCollectionName("巴斯蒂安钢琴教程技巧2");
//		dealWithCollectionName("巴斯蒂安钢琴教程技巧3");
//		dealWithCollectionName("巴斯蒂安钢琴教程演奏1");
//		dealWithCollectionName("巴斯蒂安钢琴教程演奏2");
		dealWithCollectionName("约翰汤普森简易教程2", phone);
		dealWithCollectionName("约翰汤普森现代钢琴教程1", phone2);
//		dealWithCollectionName("约翰汤普森简易教程2", phone);
//		dealWithCollectionName("约翰汤普森简易教程3", phone);
//		dealWithCollectionName("约翰汤普森简易教程2", phone);
//		dealWithCollectionName("约翰汤普森现代钢琴教程2");
	}

	public static void dealWithCollectionName(String collectionName, String phone) {

		String userId = getMemberId(phone);
		String collectionId = getCollectionId(collectionName);
		List<String> musicList = getMusicList(collectionId);

		System.out.println("collectionName: " + collectionName);
		System.out.println("phone: " + phone);
		System.out.println("userId: " + userId);
		System.out.println("collectionId: " + collectionId);
		System.out.println("musicList: " + JSONObject.toJSONString(musicList));

		System.out.println(insertCollection(collectionId, userId, phone));
		System.out.println(insertMusic(musicList, userId, phone));
	}

	public static String getMemberId(String phone) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String memberId = null;

		try {
			conn = DBUtil.getConn("mido_member");
			String sql = "select * from sm_member sm where sm.phone = '" + phone + "'";

			pstmt = DBUtil.getPStmt(conn, sql);
			rs = pstmt.executeQuery();

			while(rs.next()){
				memberId = rs.getString("id");
			}

			return memberId;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeStmt(pstmt);
			DBUtil.closeConn(conn);
		}

		return null;
	}

	public static List<String> getMusicList(String collectionId) {
		List<String> musicList = new ArrayList<String>();

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			conn = DBUtil.getConn("mido_music");
			String sql = "select * from mis_music mm where mm.music_collection_id = '" + collectionId + "'";

			pstmt = DBUtil.getPStmt(conn, sql);
			rs = pstmt.executeQuery();

			while(rs.next()){
				String id = rs.getString("id");
				musicList.add(id);
			}

			return musicList;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeStmt(pstmt);
			DBUtil.closeConn(conn);
		}

		return null;
	}

	public static String getCollectionId(String collectionName) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String id = null;

		try {
			conn = DBUtil.getConn("mido_music");
			String sql = "select * from mis_music_collection mmc " +
					"where mmc.music_collection_name = '"+ collectionName +"' and mmc.del_flag = 0";

			pstmt = DBUtil.getPStmt(conn, sql);
			rs = pstmt.executeQuery();

			while(rs.next()){
				id = rs.getString("id");
			}

			return id;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeStmt(pstmt);
			DBUtil.closeConn(conn);
		}

		return null;
	}

	public static boolean insertMusic(List<String> musicList, String userId, String phone) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String id = null;

		try {
			conn = DBUtil.getConn("mido_music");

			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			String dateString = new SimpleDateFormat("yyyyMMddhhmmss").format(timestamp);

			for (int i = 0; i < musicList.size(); i++) {
				String sql = "INSERT INTO mis_user_music (id, user_id, music_id, type, " +
						"create_time, update_time, del_flag) VALUES (?, ?, ?, ?, ?, ?, ?)";

				String lastDigit = i > 100 ? "" + i : (i < 10 ? "00" + i : "0" + i);
				String concatStr = dateString + phone.substring(0, 3) + "putong" + lastDigit;

				pstmt = DBUtil.getPStmt(conn, sql);
				pstmt.setString(1, concatStr);
				pstmt.setString(2, userId);
				pstmt.setString(3, musicList.get(i));
				pstmt.setString(4, "3");
				pstmt.setString(5, timestamp.toString());
				pstmt.setString(6, timestamp.toString());
				pstmt.setString(7, "0");

				int result = pstmt.executeUpdate();
				if (result == 0) {
					return false;
				}
			}

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeStmt(pstmt);
			DBUtil.closeConn(conn);
		}

		return false;
	}

	public static boolean insertCollection(String collectionId, String userId, String phone) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String id = null;

		try {
			conn = DBUtil.getConn("mido_music");
			String sql = "INSERT INTO mis_user_collection ( id, user_id, collection_id, type, " +
					"create_time, update_time, del_flag) VALUES (?,?,?,?,?,?,?)";

			Timestamp timestamp = new Timestamp(System.currentTimeMillis());
			String dateString = new SimpleDateFormat("yyyyMMddhhmmss").format(timestamp);
			String concatStr = dateString + phone.substring(0, 3) + "putong";

			pstmt = DBUtil.getPStmt(conn, sql);
			pstmt.setString(1, concatStr);
			pstmt.setString(2, userId);
			pstmt.setString(3, collectionId);
			pstmt.setString(4, "3");
			pstmt.setString(5, timestamp.toString());
			pstmt.setString(6, timestamp.toString());
			pstmt.setString(7, "0");

			return pstmt.executeUpdate() == 1 ? true : false;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeStmt(pstmt);
			DBUtil.closeConn(conn);
		}

		return false;
	}
}
